# 数据读取及基本处理
import pandas as pd
from pandas import DataFrame as df
import numpy as np
import datetime
#查看数据分布是否对称/计算斜度
from scipy.stats import skew

#可视化
import matplotlib.pyplot as plt
import seaborn as sns
pd.options.mode.chained_assignment = None
pd.set_option('display.max_columns', None)
#1754884 record,1053282 with coupon_id,9738 coupon. date_received:20160101~20160615,date:20160101~20160630, 539438 users, 8415 merchants
off_train = pd.read_csv('ccf_offline_stage1_train.csv',low_memory=False,na_values='null',parse_dates=['Date_received','Date'])
off_train.columns = ['user_id','merchant_id','coupon_id','discount_rate','distance','date_received','date']
#2050 coupon_id. date_received:20160701~20160731, 76309 users(76307 in trainset, 35965 in online_trainset), 1559 merchants(1558 in trainset)
off_test = pd.read_csv('ccf_offline_stage1_test_revised.csv',low_memory=False,na_values='null',parse_dates=['Date_received'])
off_test.columns = ['user_id','merchant_id','coupon_id','discount_rate','distance','date_received']
#11429826 record(872357 with coupon_id),762858 user(267448 in off_train)
# on_train = pd.read_csv('ccf_online_stage1_train.csv',header=None)
# on_train.columns = ['user_id','merchant_id','action','coupon_id','discount_rate','date_received','date']

dataset3 = off_test
feature3 =  off_train[(off_train.date_received>='20160315')&(off_train.date_received<='20160630')]
dataset2 = off_train[(off_train.date_received>='20160515')&(off_train.date_received<='20160615')]
feature2 = off_train[(off_train.date_received>='20160201')&(off_train.date_received<='20160514')]
# dataset1 = off_train[(off_train.date_received>='20160414')&(off_train.date_received<='20160514')]
# feature1 = off_train[(off_train.date>='20160101')&(off_train.date<='20160413')|((off_train.date=='NaT')&(off_train.date_received>='20160101')&(off_train.date_received<='20160413'))]

############# other feature ##################3
# 5. other feature:
#       this_month_user_receive_all_coupon_count
#       this_month_user_receive_same_coupon_count
#       this_month_user_receive_same_coupon_lastone
#       this_month_user_receive_same_coupon_firstone
#       this_day_user_receive_all_coupon_count
#       this_day_user_receive_same_coupon_count
#       day_gap_before, day_gap_after  (receive the same coupon)

#       this_month_user_receive_all_coupon_count
def get_offline_test_other_feature(dataset):
    print("enter get_offline_test_other_feature")
    starttime = datetime.datetime.now()

    # long running
    dataset['get_coupon_sum']=1
    dataset['get_coupon_sum'] = dataset[['get_coupon_sum']].groupby(dataset['user_id']).transform(lambda x:x.sum())

    endtime1 = datetime.datetime.now()
    print('get_coupon_sum cost',(endtime1 - starttime).seconds)
    dataset['get_same_coupon_sum']=1
    dataset['get_same_coupon_sum'] = dataset.groupby(['user_id','coupon_id'])['get_same_coupon_sum'].transform(lambda x:x.sum())
    endtime2 = datetime.datetime.now()
    print('get_same_coupon_sum cost',(endtime2 - endtime1).seconds)

    dataset['same_coupon_timedelta_max'] = dataset.groupby(['user_id','coupon_id'])['date_received'].transform(lambda x:x.max())
    dataset['same_coupon_timedelta_max_date_received'] = dataset['same_coupon_timedelta_max'] -dataset['date_received']
    endtime3 = datetime.datetime.now()
    print('same_coupon_timedelta_min_date_received cost',(endtime3 - endtime2).seconds)
    dataset['same_coupon_timedelta_min'] = dataset.groupby(['user_id','coupon_id'])['date_received'].transform(lambda x:x.min())
    dataset['same_coupon_timedelta_min_date_received'] = dataset['date_received'] -dataset['same_coupon_timedelta_min']
    endtime4 = datetime.datetime.now()
    print('same_coupon_timedelta_min_date_received cost',(endtime4 - endtime3).seconds)

    dataset['this_day_user_receive_all_coupon_count'] = 1
    dataset['this_day_user_receive_all_coupon_count'] = dataset.groupby(['user_id','date_received'])['this_day_user_receive_all_coupon_count'].transform(lambda x:x.sum())
    endtime5 = datetime.datetime.now()
    print('this_day_user_receive_same_coupon_count cost',(endtime5 - endtime4).seconds)

    dataset['this_day_user_receive_same_coupon_count'] = 1
    dataset['this_day_user_receive_same_coupon_count'] = dataset.groupby(['user_id','coupon_id','date_received'])['this_day_user_receive_same_coupon_count'].transform(lambda x:x.sum())
    endtime6 = datetime.datetime.now()
    print('this_day_user_receive_same_coupon_count cost',(endtime6 - endtime5).seconds)

    def get_day_gap_before(groupby_serices):
        if(len(groupby_serices.index)>1):
            groupby_serices_index=groupby_serices.index
            groupby_serices_len=len(groupby_serices.index)
            day_before=groupby_serices.copy()
            # if(groupby_serices.index.max()<=10):
            #     print('day_before is ',day_before)
            #     print('day_before type is ',type(day_before))
            day_before[groupby_serices_index[1:groupby_serices_len]]=groupby_serices[groupby_serices_index[0:groupby_serices_len-1]]
            day_before[groupby_serices_index[0]]=groupby_serices[groupby_serices_index[0]]
            return (groupby_serices-day_before).dt.days
        else:
            return -1
    dataset['day_gap_before'] = dataset.sort_values(['date_received']).groupby(['user_id', 'coupon_id'])['date_received'].transform(get_day_gap_before)
    endtime7 = datetime.datetime.now()
    print('day_gap_before cost',(endtime7 - endtime6).seconds)

    def get_day_gap_after(groupby_serices):
        if(len(groupby_serices.index)>1):
            groupby_serices_index=groupby_serices.index
            groupby_serices_len=len(groupby_serices.index)
            day_after=groupby_serices.copy()
            day_after[groupby_serices_index[0:groupby_serices_len-1]]=groupby_serices[groupby_serices_index[1:groupby_serices_len]]
            day_after[groupby_serices_index[groupby_serices_len-1]]=groupby_serices[groupby_serices_index[groupby_serices_len-1]]
            # if(groupby_serices.index.max()<=10):
            #     print('day_before is ',day_after)
            #     print('day_before type is ',type(day_after))
            return (day_after-groupby_serices).dt.days
        else:
            return -1
    dataset['day_gap_after'] = dataset.sort_values(['date_received']).groupby(['user_id', 'coupon_id'])['date_received'].transform(get_day_gap_after)
    dataset['day_gap_after'] =pd.to_numeric(dataset['day_gap_after'])
    endtime8 = datetime.datetime.now()
    print('day_gap_after cost',(endtime8 - endtime7).seconds)

    return dataset
############# coupon related feature   #############
# """
# 2.coupon related:
#       discount_rate. discount_man. discount_jian. is_man_jian
#       day_of_week,day_of_month. (date_received)
# """
#       discount_rate
def get_offline_test_coupon_feature(dataset):
    print('enter get_offline_test_coupon_feature')
    def calc_discount_rate(s):
        s =str(s)
        s = s.split(':')
        if len(s)==1:
            return float(s[0])
        else:
            return 1.0-float(s[1])/float(s[0])
    def get_discount_man(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 'null'
        else:
            return int(s[0])
    def get_discount_jian(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 'null'
        else:
            return int(s[1])
    def is_man_jian(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 0
        else:
            return 1
    dataset['calc_discount_rate']=dataset['discount_rate'].map(lambda x:calc_discount_rate(x))
    dataset['get_discount_man']=dataset['discount_rate'].map(lambda x:get_discount_man(x))
    dataset['get_discount_jian']=dataset['discount_rate'].map(lambda x:get_discount_jian(x))
    dataset['is_man_jian']=dataset['discount_rate'].map(lambda x:is_man_jian(x))

    dataset["month"] =dataset['date_received'].dt.month
    dataset["day"] =dataset['date_received'].dt.day
    dataset["weekofyear"] =dataset['date_received'].dt.weekofyear
    dataset["dayofweek"] = dataset['date_received'].dt.dayofweek
    dataset["weekday"] =dataset['date_received'].dt.weekday
    dataset["dayofyear"] =dataset['date_received'].dt.dayofyear
    dataset["daysinmonth"] = dataset['date_received'].dt.daysinmonth

    dataset['coupon_count']=1
    dataset['coupon_count']=dataset.groupby(['coupon_id'])['coupon_count'].transform(lambda x:x.sum())
    return dataset
############# merchant related feature   #############
# """
# 1.merchant related:
#       total_sales. sales_use_coupon.  total_coupon
#       coupon_rate = sales_use_coupon/total_sales.
#       transfer_rate = sales_use_coupon/total_coupon.
#       merchant_avg_distance,merchant_min_distance,merchant_max_distance of those use coupon
# """
def get_offline_test_merchant_feature(dataset):
    print("enter get_offline_test_merchant_feature")
    dataset['total_coupon']=0
    dataset['total_coupon'][dataset['coupon_id']!=np.nan]=1
    dataset['total_coupon']=dataset.groupby(['merchant_id'])['total_coupon'].transform(lambda x:x.sum())

    dataset['merchant_min_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.min())
    dataset['merchant_max_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.max())
    dataset['merchant_mean_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.mean())
    dataset['merchant_median_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.median())
    return dataset
##################  user_merchant related feature #########################
# 4.user_merchant:
#       times_user_buy_merchant_before.
# all_user_merchant = dataset[['user_id','merchant_id']]
# all_user_merchant.drop_duplicates(inplace=True)
def get_offline_test_user_merchant(dataset):
    print("enter get_offline_test_user_merchant")
    dataset['user_merchant_any']=1
    dataset['user_merchant_any']=dataset.groupby(['user_id','merchant_id'])['user_merchant_any'].transform(lambda x:x.sum())

    return dataset
############# user related feature   #############
# 3.user related:
#       count_merchant.
#       user_avg_distance, user_min_distance,user_max_distance.
#       buy_use_coupon. buy_total. coupon_received.
#       buy_use_coupon/coupon_received.
#       buy_use_coupon/buy_total
#       user_date_datereceived_gap
def get_offline_test_user_feature(dataset):
    print("enter get_offline_test_user_feature")

    dataset['user_min_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.min())
    dataset['user_max_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.max())
    dataset['user_mean_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.mean())
    dataset['user_median_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.median())

    dataset['coupon_received']=0
    dataset['coupon_received'][dataset['coupon_id']!=np.nan]=1
    dataset['coupon_received']=dataset.groupby(['user_id'])['coupon_received'].transform(lambda x:x.sum())

    return dataset

def get_offline_train_other_feature(dataset):
    print("enter get_offline_train_other_feature")
    dataset['get_coupon_sum']=1
    dataset['get_coupon_sum'] = dataset.groupby(['user_id'])['get_coupon_sum'].transform(lambda x:x.sum())
    #       this_month_user_receive_same_coupon_count
    dataset['get_same_coupon_sum']=1
    dataset['get_same_coupon_sum'] = dataset.groupby(['user_id','coupon_id'])['get_same_coupon_sum'].transform(lambda x:x.sum())

    dataset['same_coupon_timedelta_max_date_received'] = dataset.groupby(['user_id','coupon_id'])['date_received'].transform(lambda x:(x.max() - x).dt.days)
    dataset['same_coupon_timedelta_min_date_received'] = dataset.groupby(['user_id','coupon_id'])['date_received'].transform(lambda x:(x- x.min()).dt.days)

    dataset['this_day_user_receive_all_coupon_count'] = 1
    dataset['this_day_user_receive_all_coupon_count'] = dataset.groupby(['user_id','date_received'])['this_day_user_receive_all_coupon_count'].transform(lambda x:x.sum())
    #       this_day_user_receive_same_coupon_count
    dataset['this_day_user_receive_same_coupon_count'] = 1
    dataset['this_day_user_receive_same_coupon_count'] = dataset.groupby(['user_id','coupon_id','date_received'])['this_day_user_receive_same_coupon_count'].transform(lambda x:x.sum())
    #       day_gap_before, (receive the same coupon)
    def get_day_gap_before(groupby_serices):
        if(len(groupby_serices.index)>1):
            groupby_serices_index=groupby_serices.index
            groupby_serices_len=len(groupby_serices.index)
            day_before=groupby_serices.copy()
            # if(groupby_serices.index.max()<=10):
            #     print('day_before is ',day_before)
            #     print('day_before type is ',type(day_before))
            day_before[groupby_serices_index[1:groupby_serices_len]]=groupby_serices[groupby_serices_index[0:groupby_serices_len-1]]
            day_before[groupby_serices_index[0]]=groupby_serices[groupby_serices_index[0]]
            return (groupby_serices-day_before).dt.days
        else:
            return -1
    dataset['day_gap_before'] = dataset.sort_values(['date_received']).groupby(['user_id', 'coupon_id'])['date_received'].transform(get_day_gap_before)
    # day_gap_after  (receive the same coupon)
    def get_day_gap_after(groupby_serices):
        if(len(groupby_serices.index)>1):
            groupby_serices_index=groupby_serices.index
            groupby_serices_len=len(groupby_serices.index)
            day_after=groupby_serices.copy()
            day_after[groupby_serices_index[0:groupby_serices_len-1]]=groupby_serices[groupby_serices_index[1:groupby_serices_len]]
            day_after[groupby_serices_index[groupby_serices_len-1]]=groupby_serices[groupby_serices_index[groupby_serices_len-1]]
            # if(groupby_serices.index.max()<=10):
            #     print('day_before is ',day_after)
            #     print('day_before type is ',type(day_after))
            return (day_after-groupby_serices).dt.days
        else:
            return -1
    dataset['day_gap_after'] = dataset.sort_values(['date_received']).groupby(['user_id', 'coupon_id'])['date_received'].transform(get_day_gap_after)
    dataset['day_gap_after'] =pd.to_numeric(dataset['day_gap_after'])
    return dataset
############# coupon related feature   #############
# """
# 2.coupon related:
#       discount_rate. discount_man. discount_jian. is_man_jian
#       day_of_week,day_of_month. (date_received)
# """
#       discount_rate
def get_offline_train_coupon_feature(dataset):
    print('enter get_offline_train_coupon_feature')
    def calc_discount_rate(s):
        s =str(s)
        s = s.split(':')
        if len(s)==1:
            return float(s[0])
        else:
            return 1.0-float(s[1])/float(s[0])
    def get_discount_man(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 'null'
        else:
            return int(s[0])
    def get_discount_jian(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 'null'
        else:
            return int(s[1])
    def is_man_jian(s):
        s = str(s)
        s = s.split(':')
        if len(s) == 1:
            return 0
        else:
            return 1
    dataset['calc_discount_rate']=dataset['discount_rate'].map(lambda x:calc_discount_rate(x))
    dataset['get_discount_man']=dataset['discount_rate'].map(lambda x:get_discount_man(x))
    dataset['get_discount_jian']=dataset['discount_rate'].map(lambda x:get_discount_jian(x))
    dataset['is_man_jian']=dataset['discount_rate'].map(lambda x:is_man_jian(x))

    dataset["month"] =dataset['date_received'].dt.month
    dataset["day"] =dataset['date_received'].dt.day
    dataset["weekofyear"] =dataset['date_received'].dt.weekofyear
    dataset["dayofweek"] = dataset['date_received'].dt.dayofweek
    dataset["weekday"] =dataset['date_received'].dt.weekday
    dataset["dayofyear"] =dataset['date_received'].dt.dayofyear
    dataset["daysinmonth"] = dataset['date_received'].dt.daysinmonth

    dataset['coupon_count']=1
    dataset['coupon_count']=dataset.groupby(['coupon_id'])['coupon_count'].transform(lambda x:x.sum())
    return dataset
############# merchant related feature   #############
# """
# 1.merchant related:
#       total_sales. sales_use_coupon.  total_coupon
#       coupon_rate = sales_use_coupon/total_sales.
#       transfer_rate = sales_use_coupon/total_coupon.
#       merchant_avg_distance,merchant_min_distance,merchant_max_distance of those use coupon
# """
def get_offline_train_merchant_feature(dataset):
    print('enter get_offline_train_merchant_feature')

    dataset['total_sales']=0
    dataset['total_sales'][dataset['date']!='NaT']=1
    dataset['total_sales']=dataset.groupby(['merchant_id'])['total_sales'].transform(lambda x:x.sum())

    dataset['sales_use_coupon']=0
    dataset['sales_use_coupon'][(dataset['date']!='NaT')&(dataset['coupon_id']!=np.nan)]=1
    dataset['sales_use_coupon']=dataset.groupby(['merchant_id'])['sales_use_coupon'].transform(lambda x:x.sum())

    dataset['total_coupon']=0
    dataset['total_coupon'][dataset['coupon_id']!=np.nan]=1
    dataset['total_coupon']=dataset.groupby(['merchant_id'])['total_coupon'].transform(lambda x:x.sum())

    dataset['merchant_min_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.min())
    dataset['merchant_max_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.max())
    dataset['merchant_mean_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.mean())
    dataset['merchant_median_distance']=dataset.groupby(['merchant_id'])['distance'].transform(lambda x:x.median())
    return dataset
##################  user_merchant related feature #########################
# 4.user_merchant:
#       times_user_buy_merchant_before.
# all_user_merchant = dataset[['user_id','merchant_id']]
# all_user_merchant.drop_duplicates(inplace=True)
def get_offline_train_user_merchant(dataset):
    print('enter get_offline_train_user_merchant')

    dataset['user_merchant_buy_total']=0
    dataset['user_merchant_buy_total'][dataset['date']!='NaT']=1
    dataset['user_merchant_buy_total']=dataset.groupby(['user_id','merchant_id'])['user_merchant_buy_total'].transform(lambda x:x.sum())

    dataset['user_merchant_buy_use_coupon']=0
    dataset['user_merchant_buy_use_coupon'][(dataset['date']!='NaT')&(dataset['date_received']!='NaT')]=1
    dataset['user_merchant_buy_use_coupon']=dataset.groupby(['user_id'])['user_merchant_buy_use_coupon'].transform(lambda x:x.sum())

    dataset['user_merchant_buy_common']=0
    dataset['user_merchant_buy_common'][(dataset['date']!='NaT')&(dataset['coupon_id']!=np.nan)]=1
    dataset['user_merchant_buy_common']=dataset.groupby(['user_id'])['user_merchant_buy_common'].transform(lambda x:x.sum())

    dataset['user_merchant_any']=1
    dataset['user_merchant_any']=dataset.groupby(['user_id','merchant_id'])['user_merchant_any'].transform(lambda x:x.sum())

    return dataset
############# user related feature   #############
# 3.user related:
#       count_merchant.
#       user_avg_distance, user_min_distance,user_max_distance.
#       buy_use_coupon. buy_total. coupon_received.
#       buy_use_coupon/coupon_received.
#       buy_use_coupon/buy_total
#       user_date_datereceived_gap
def get_offline_train_user_feature(dataset):
    print('enter get_offline_train_user_feature')

    t4 = dataset[dataset.date!='NaT'][['user_id','merchant_id']]
    t4.drop_duplicates(inplace=True)
    t4.merchant_id = 1
    t4 = t4.groupby('user_id').agg('sum').reset_index()
    t4.rename(columns={'merchant_id':'count_merchant'},inplace=True)
    dataset=pd.merge(dataset,t4,on='user_id',how='left')

    dataset['buy_total']=0
    dataset['buy_total'][dataset['date']!='NaT']=1
    dataset['buy_total']=dataset.groupby(['user_id'])['buy_total'].transform(lambda x:x.sum())

    dataset['buy_use_coupon']=0
    dataset['buy_use_coupon'][(dataset['date']!='NaT')&(dataset['coupon_id']!=np.nan)]=1
    dataset['buy_use_coupon']=dataset.groupby(['user_id'])['buy_use_coupon'].transform(lambda x:x.sum())

    dataset['coupon_received']=0
    dataset['coupon_received'][dataset['coupon_id']!=np.nan]=1
    dataset['coupon_received']=dataset.groupby(['user_id'])['coupon_received'].transform(lambda x:x.sum())

    dataset['user_date_datereceived_gap'] = pd.to_numeric((dataset['date'] - dataset['date_received']).dt.days)
    dataset['avg_user_date_datereceived_gap'] = dataset.groupby(['user_id'])[
        'user_date_datereceived_gap'].transform(lambda x: x.mean())
    dataset['min_user_date_datereceived_gap'] = dataset.groupby(['user_id'])[
        'user_date_datereceived_gap'].transform(lambda x: x.min())
    dataset['max_user_date_datereceived_gap'] = dataset.groupby(['user_id'])[
        'user_date_datereceived_gap'].transform(lambda x: x.max())
    dataset['buy_use_coupon_rate'] = dataset.buy_use_coupon.astype('float') / dataset.buy_total.astype('float')
    dataset['user_coupon_transfer_rate'] = dataset.buy_use_coupon.astype('float') / dataset.coupon_received.astype('float')

    dataset['user_min_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.min())
    dataset['user_max_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.max())
    dataset['user_mean_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.mean())
    dataset['user_median_distance']=dataset.groupby(['user_id'])['distance'].transform(lambda x:x.median())

    return dataset

dataset3=get_offline_test_other_feature(dataset3)
dataset3=get_offline_test_coupon_feature(dataset3)
dataset3=get_offline_test_merchant_feature(dataset3)
dataset3=get_offline_test_user_merchant(dataset3)
dataset3=get_offline_test_user_feature(dataset3)
columns_name=list(dataset3.columns)
for i in range(6,len(columns_name)):
    columns_name[i]=columns_name[i]+'_this_month'
dataset3.columns=columns_name
print('columns is',dataset3.columns)
print('before dataset3 shape is ',dataset3.shape)
dataset3.to_csv('dataset3.csv')
feature3=get_offline_train_other_feature(feature3)
print("111",feature3.columns)
other_feature=feature3[['user_id','coupon_id','get_same_coupon_sum']]
dataset3 = pd.merge(dataset3,other_feature,on=['user_id','coupon_id'],how='left')
print('after dataset3 shape is ',dataset3.shape)
feature3.to_csv('feature3.csv')
# feature3=get_offline_train_coupon_feature(feature3)
# print("222",feature3.columns)
# feature3=get_offline_train_merchant_feature(feature3)
# print("333",feature3.columns)
# feature3=get_offline_train_user_merchant(feature3)
# print("444",feature3.columns)
# feature3=get_offline_train_user_feature(feature3)
# print("555",feature3.columns)

print(dataset3.head(40))
print(feature3.head(40))
